/**
 * Class:   OrganizationManager
 * 
 * Methods:
 * 
 *      public boolean insertOrganization( Organization organization );
 *      
 *      public ArrayList<Organization> viewOrganization();
 *      
 *      public ArrayList<Organization> searchOrganization( int activityID );
 *      
 *      public boolean updateOrganization( Organization original,
 *      								   Organization changed );
 *      
 *      public int getOrganizationID( Organization organization );
 * 
 * Remarks:
 * 
 * 		This class can be touched by the controllers.
 * 
 * @author ( Sharmaine Lim )
 * @version ( 2013-02-28 // YYYY-MM-DD )
 */

package managers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import models.Organization;

public class OrganizationManager extends DatabaseManager {
    
    /**
     * Constructors
     */
    
    public OrganizationManager() {}
    
    
    
    /**
     *  Insert Organization
     *  	- inserts an organization into the organization database
     *  
     *  Parameters
     *		- Organization organization: the organization to be added
     *	
     *	Returns
     *		- boolean true: organization is successfully inserted
     * 		- boolean false: insertion failed
     */
    public boolean insertOrganization( Organization organization ) {
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
    		
    		connect();
    		
    		preparedStatement = getConnection()
    							.prepareStatement( "INSERT INTO" +
    											   " `organization`" +
    											   " (`organizationName`," +
    											   " `organizationType`," +
    											   " `active`)" +
    											   " VALUES (?, ?, ?)" );

    		preparedStatement.setString( 1, organization.getName() );
    		preparedStatement.setString( 2, organization.getType() );
    		preparedStatement.setBoolean( 3, organization.isActive() );
    		preparedStatement.executeUpdate();
    		
    		disconnect();
    		
    		if ( getOrganizationID( organization ) != 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  View Organization
     *  	- gets all organizations from the organization database
     *  
     *  Parameters
     *		- n/a
     *	
     *	Returns
     *		- ArrayList<Organization>.size() == 0: no organizations found
     * 		- ArrayList<Organization>.size() > 0: organizations found
     */
    public ArrayList<Organization> viewOrganization() {
    	
		/* Declaration of Local Variables */
    	ArrayList<Organization> organizationList = new ArrayList<Organization>();
    	
    	try {
    		/* Declaration of Block Variables */
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		resultSet = statement.executeQuery( "SELECT * FROM `organization`" );
    		
    		while ( resultSet.next() ) {
				organizationList.add( new Organization
								( resultSet.getString( "organizationName" ),
								  resultSet.getString( "organizationType" ),
								  resultSet.getBoolean( "active" ) ) );
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
    	}
    	return organizationList;
    }
    
    
    
    /**
     *  Search Organization
     *  	- gets all organizations in a certain activity
     *  	  from the organization database
     *  
     *  Parameters
     *		- int activityID: the ID of the activity the organizations
     *						  are from 
     *	
     *	Returns
     *		- ArrayList<Organization>.size() == 0: no organizations found
     * 		- ArrayList<Organization>.size() > 0: organizations found
     */
    public ArrayList<Organization> searchOrganization( int activityID ) {
    	
		/* Declaration of Local Variables */
    	ArrayList<Organization> organizationList = new ArrayList<Organization>();
    	
    	try {
    		/* Declaration of Block Variables */
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		resultSet = statement
    					.executeQuery( "SELECT * FROM `organization` WHERE" +
    								   " `organizationID` IN" +
    								   " (SELECT `organizationID`" +
    								   " FROM `v_activityOrganization` WHERE" +
    								   " `activityID` = " + activityID + ")" );
    		
    		while ( resultSet.next() ) {
				organizationList.add( new Organization
								( resultSet.getString( "organizationName" ),
								  resultSet.getString( "organizationType" ),
								  resultSet.getBoolean( "active" ) ) );
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
    	}
    	return organizationList;
    }
    
    
    
    /**
     *  Update Organization
     *  	- updates an organization in the organization database
     *  
     *  Parameters
     *		- Organization original: the old details of the
     *				organization to be changed
     *		- Organization changed: the new details of the
     *				organization to be changed
     *	
     *	Returns
     *		- boolean true: organization is successfully updated
     * 		- boolean false: updating failed
     */
    public boolean updateOrganization( Organization original,
    								   Organization changed ) {
		
    	/* Declaration of Local Variables */
    	int organizationID = getOrganizationID( original );
    	
    	if ( organizationID == 0 ) {
    		return false;
    	}
    	
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
    		
    		connect();
    		
    		preparedStatement = getConnection().prepareStatement
    							( "UPDATE `organization`" +
    							  " SET `organizationName` = ?," +
							      " organizationType = ?, `active` = ?" +
    							  " WHERE `organizationID` = ?" );

    		preparedStatement.setString( 1, changed.getName() );
    		preparedStatement.setString( 2, changed.getType() );
    		preparedStatement.setBoolean( 3, changed.isActive() );
    		preparedStatement.setInt( 4, organizationID );
    		preparedStatement.executeUpdate();
    		
    		disconnect();
    		
    		if ( getOrganizationID( changed ) != 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  Delete Organization
     *  	- deletes an organization from the organization database
     *  
     *  Parameters
     *		- Organization organization: the organization to be
     *									 deleted
     *	
     *	Returns
     *		- boolean true: organization is successfully deleted
     * 		- boolean false: deletion failed
     */
    public boolean deleteOrganization( Organization organization ) {
		
    	/* Declaration of Local Variables */
    	int organizationID = getOrganizationID( organization );
    	
    	if ( organizationID == 0 ) {
    		return false;
    	}
    	
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
    		
    		connect();
    		
    		preparedStatement = getConnection()
    							.prepareStatement( "DELETE FROM" +
    											   " `organization`" +
    											   " WHERE `organizationID`" +
    											   " = ?" );

    		preparedStatement.setInt( 1, organizationID );
    		preparedStatement.executeUpdate();
    		
    		disconnect();
    		
    		if ( getOrganizationID( organization ) == 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  Get Organization ID
     *  	- finds the ID of the organization in the organization database
     *  
     *  Parameters
     *		- Organization organization: the organization to be found
     *	
     *	Returns
     *		- non-zero int: organization ID found
     * 		- int 0: organization not found
     */
    public int getOrganizationID( Organization organization ) {
    	try {
    		/* Declaration of Block Variables */
    		int organizationID = 0;
    		int active = 0;
    		String query;
    		Statement statement;
    		ResultSet resultSet;

    		if ( organization.isActive() ) {
    			active = 1;
    		}
    		
    		query = "SELECT `organizationID` FROM `organization`" +
					" WHERE `organizationName` = \"" + organization.getName() +
					"\" AND `active` = " + active;
    		
    		if ( !organization.getType().equals( "" ) ) {
    			query = query.concat( " AND `organizationType` = \"" +
    								  organization.getType() + "\"" );
    		}
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		resultSet = statement.executeQuery( query );
    		
    		while ( resultSet.next() ) {
    			organizationID = resultSet.getInt( "organizationID" );
    		}
    		
    		disconnect();
    		return organizationID;
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return 0;
    }
    
}
